package m.common.model.util;

import api.model.ApiColumn;
import api.model.ApiTable;
import m.system.cache.CacheUtil;
import m.system.db.DBManager;
import m.system.db.DataRow;
import m.system.db.DataSet;
import m.system.exception.MException;
import m.system.util.ObjectUtil;
import m.system.util.StringUtil;
import m.system.util.SystemUtil;

import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.*;

public class TableQueryList implements IModelQuery,QueryList {

    private final List<QueryCondition> conditions;
    private final List<QueryOrder> orders;
    private QueryPage page;
    private int int_a=0;

    private QueryParameter parameter;
    private QueryParameter countParameter;
    private final ApiTable apiTable;
    private final List<String> fieldNames;
    private List<String> fieldNameSqlList;
    private final Map<String,String> fieldTypeMap;
    private final Map<String,String> tableAMap;
    private final Map<String,String> linkTableSqlMap;
    private final Map<String,String> fieldToAliasMap;
    private final List<IModelQuery> unionList;

    private TableQueryList(String tableOid, String[] fieldNames,QueryPage page,QueryCondition condition,QueryOrder... orders) {
        this.apiTable= CacheUtil.get(ApiTable.class,tableOid);
        this.page=page;
        this.conditions=new ArrayList<>();
        if(null!=condition) this.conditions.add(condition);
        this.orders=new ArrayList<>();
        if(null!=orders&&orders.length>0) this.orders.addAll(Arrays.asList(orders));
        this.fieldNames= new ArrayList<>();
        this.tableAMap= new HashMap<>();
        this.linkTableSqlMap= new LinkedHashMap<>();
        this.fieldToAliasMap= new HashMap<>();
        this.fieldTypeMap=new HashMap<>();
        this.unionList=new ArrayList<>();
        addField(fieldNames);
    }


    @Override
    public TableQueryList addField(String... fieldName) {
        for(String f : fieldName) {
            if(!StringUtil.isSpace(f)) {
                List<String> ls=coverToFieldList(this.apiTable, "", f);
                for(String fn : ls) {
                    if(!this.fieldNames.contains(fn)) {
                        this.fieldNames.add(fn);
                    }
                }
            }
        }
        return this;
    }
    @Override
    public TableQueryList unionAll(IModelQuery... querys) {
        this.unionList.addAll(Arrays.asList(querys));
        return this;
    }

    @Deprecated
    @Override
    public IModelQuery addFieldExpression(String field, String expression) {
        throw new RuntimeException("该方法无效，无需设置。");
    }

    @Override
    public TableQueryList addCondition(QueryCondition... cond) {
        this.conditions.addAll(Arrays.asList(cond));
        return this;
    }

    @Override
    public TableQueryList setPage(QueryPage page) {
        this.page=page;
        return this;
    }

    @Override
    public TableQueryList addOrder(QueryOrder... order) {
        this.orders.addAll(Arrays.asList(order));
        return this;
    }

    //转换属性，*转换成所有属性
    private List<String> coverToFieldList(ApiTable at, String prefix, String attr){
        List<String> fieldList= new ArrayList<>();
        if(null==at) return fieldList;
        String ps=StringUtil.isSpace(prefix)?"": prefix + ".";
        if("*".equals(attr)){
            fieldList.add(ps + "oid");
            List<ApiColumn> fs=at.getSource().equals("Y")?
                CacheUtil.getList(ApiColumn.class,at.getSourceClass())
                :CacheUtil.getList(ApiColumn.class,at.getOid());
            for(ApiColumn col : fs){
                switch (col.getType()){
                    case "TABLE":
                    case "MODEL":
                        fieldList.add(ps + col.getName() + ".oid");
                        break;
                    default:
                        fieldList.add(ps + col.getName());
                }
            }
        }else{
            int i=attr.indexOf(".");
            if(i==-1){
                fieldList.add(ps + attr);
            }else{
                String fn=attr.substring(0, i);
                String newField=attr.substring(i+1);
                ApiColumn col=CacheUtil.get(ApiColumn.class,at.getOid(),fn);
                if(null!=col){
                    ApiTable modelTable=col.getModelTable();
                    if(null==modelTable||StringUtil.isSpace(modelTable.getOid())){
                        modelTable=CacheUtil.get(ApiTable.class,col.getModelClass());
                    }
                    fieldList.addAll(coverToFieldList(modelTable, ps + fn, newField));
                }
            }
        }
        return fieldList;
    }
    /**
     * 获取fieldNameSql对应的数据类型
     */
    protected String getFieldType(String fieldNameSql){
        String type=this.fieldTypeMap.get(fieldNameSql);
        return null==type?"STRING":type;
    }
    /**
     * 递归方法,  返回对应的查询的fieldName, 并添加需要left join的 table
     */
    protected String getFieldNameSql(String a,String fieldName,ApiTable at,boolean hasColName) throws MException{
        if("oid".equals(fieldName)){
            if(hasColName){
                return a + "."+at.oidField()+" " + a + "_oid";
            }else{
                return a + "."+at.oidField();
            }
        }
        int i=fieldName.indexOf(".");
        if(i==-1){
            ApiColumn col=CacheUtil.get(ApiColumn.class,at.getOid(),fieldName);
            if(null!=col){
                String fieldNameSql="";
                if(hasColName){
                    fieldNameSql= a + "." + col.getField() + " " + a + "_" + fieldName;
                }else{
                    fieldNameSql= a + "." + col.getField();
                }
                this.fieldTypeMap.put(fieldNameSql,col.getType());
                return fieldNameSql;
            }
        }else{
            String fn=fieldName.substring(0, i);
            String newField=fieldName.substring(i+1);
            ApiColumn col=CacheUtil.get(ApiColumn.class,at.getOid(),fn);
            if(null!=col){
                String afn= a + "." + fn;
                if(newField.equals("oid")){
                    if(hasColName){
                        return a + "." + col.getField() + " " + a + "_" + fn;
                    }else{
                        return a + "." + col.getField();
                    }
                }else{
                    ApiTable atable;
                    if(null!=col.getModelTable()&&!StringUtil.isSpace(col.getModelTable().getOid())) {
                        atable = col.getModelTable();
                    }else {
                        atable=CacheUtil.get(ApiTable.class,col.getModelClass());
                    }
                    String linka=this.tableAMap.get(afn);
                    if(null==linka){
                        String tn=StringUtil.isSpace(atable.getSourceName())?atable.getOid():atable.getSourceName();
                        linka= "t" + (int_a + 1);
                        String linkTableSql = " LEFT JOIN " + tn + " " + linka +
                            " ON t" + (int_a + 1) + "."+atable.oidField()+"=" + a + "." + col.getField();
                        this.linkTableSqlMap.put(afn, linkTableSql);
                        this.tableAMap.put(afn, linka);
                        int_a++;
                    }
                    return getFieldNameSql(linka, newField, atable, hasColName);
                }
            }else {
                throw new MException(this.getClass(), "没有找到属性 " + fieldName);
            }
        }
        return null;
    }
    /**
     * 获取fieldNames对应的sqlList
     * 
     *  MException
     */
    private List<String> getFieldNameSqlList() throws MException{
        if(null==this.fieldNameSqlList){
            List<String> fieldNameSqlList= new ArrayList<>();
            if(null!=this.fieldNames){
                for(String fieldName : this.fieldNames){
                    String fn=getFieldNameSql("t0", fieldName, this.apiTable, true);
                    if(!StringUtil.isSpace(fn)){
                        fieldToAliasMap.put(fieldName, fn.substring(fn.lastIndexOf(" ")+1));
                        fieldNameSqlList.add(fn);
                    }
                }
            }
            this.fieldNameSqlList=fieldNameSqlList;
        }
        return this.fieldNameSqlList;
    }

    /**
     * 返回 from段sql,  先执行getFieldNameSqlList()再调用才有效
     * 
     */
    private String getFromSql(){
        StringBuilder from=new StringBuilder();
        String tn=StringUtil.isSpace(apiTable.getSourceName())?apiTable.getOid():apiTable.getSourceName();
        from.append(" FROM ").append(tn).append(" t0 ");
        if(null!=this.linkTableSqlMap){
            for(String linkTableSql : this.linkTableSqlMap.values()){
                from.append(linkTableSql);
            }
        }
        return from.toString();
    }
    @Override
    public String getAlias4Field(String field) {
        String str=this.fieldToAliasMap.get(field);
        if(null==str) {
            for(int i=0;i<this.fieldNames.size();i++) {
                if(this.fieldNames.get(i).equals(field)) {
                    String fs=this.fieldNameSqlList.get(i);
                    str=fs.substring(fs.lastIndexOf(" ")+1);
                    break;
                }
            }
        }
        return str;
    }

    @Override
    public QueryParameter getQueryParameter() throws MException {
        if(null==this.parameter){
            List<String> fnSqlList=getFieldNameSqlList();
            if(!this.conditions.isEmpty()){
                this.parameter=QueryCondition.and(this.conditions.toArray(new QueryCondition[0])).toQueryParameter("t0", this.apiTable, this);
            }else{
                this.parameter=new QueryParameter("", new ArrayList<>());
            }
            StringBuilder orderSql=new StringBuilder();
            if(null!=this.orders){
                for(QueryOrder order : this.orders){
                    if(null!=order&&!StringUtil.isSpace(order.getName())) {
                        String os=order.toSqlString("t0", this.apiTable, this);
                        orderSql.append(os).append(",");
                    }
                }
                orderSql.append(QueryOrder.desc(apiTable.oidField()).toSqlString("t0", this.apiTable, this));
            }
            if(fnSqlList.isEmpty()) throw new MException(this.getClass(), "未设置查询字段或字段无效");
            String from=getFromSql();
            StringBuilder sql=new StringBuilder();
            for(String fnSql : fnSqlList){
                sql.append(",").append(fnSql);
            }
            sql=new StringBuilder(sql.substring(1)).insert(0, "SELECT ").append(from);
            if(!StringUtil.isSpace(this.parameter.getSql())){
                sql.append(" WHERE ").append(this.parameter.getSql());
            }
            if(!unionList.isEmpty()) {
                for (IModelQuery query : unionList) {
                    QueryParameter qp = query.getQueryParameter();
                    sql.append(" UNION ALL ").append(qp.getSubSql());
                    this.parameter.getValueList().addAll(qp.getValueList());
                }
            }else {
                if (orderSql.length() > 0) {
                    sql.append(" ORDER BY ").append(orderSql);
                }
                if (null != this.page) {
                    sql.append(" LIMIT ").append(this.page.getIndex()).append(",").append(this.page.getNum()).append(" ");
                }
            }
            this.parameter.setSql(sql.toString());
        }
        return this.parameter;
    }
    public QueryParameter getCountParameter() throws MException{
        if(null==this.countParameter){
            getQueryParameter();
            this.countParameter=new QueryParameter(this.parameter.getSql(), this.parameter.getValueList());
            this.countParameter.setSql("SELECT count(*) num FROM (" + this.countParameter.getSubSql() + ") a");
        }
        return this.countParameter;
    }

    /**
     * 返回查询实例化
     */
    public static TableQueryList instance(String name,String[] fieldNames,QueryPage page,QueryCondition condition) throws Exception {
        return new TableQueryList(name,fieldNames,page,condition);
    }
    /**
     * 返回查询实例化
     */
    public static TableQueryList instance(String name,String[] fieldNames,QueryPage page,QueryCondition condition,QueryOrder... orders) throws Exception {
        return new TableQueryList(name,fieldNames,page,condition,orders);
    }
    /**
     * 查询列表
     */
    public static List<Map<String,Object>> getList(TableQueryList tableQueryList) throws SQLException, MException{
        QueryParameter qp=tableQueryList.getQueryParameter();
        DataSet ds= DBManager.executeQuery(qp.getSql(), qp.getValueList().toArray(new Object[]{}));
        List<Map<String,Object>> list= new ArrayList<>();
        for(DataRow row : ds.rows()) {
            Map<String,Object> map= new LinkedHashMap<>();
            for(String fn : tableQueryList.fieldNames){
                fillAttribute(map, "t0" , fn, tableQueryList.tableAMap, row);
            }
            list.add(map);
        }
        return list;
    }

    /**
     * 查询列表
     */
    public static List<Map<String,Object>> getList(String name,String[] fieldNames,QueryPage page,QueryCondition condition,QueryOrder... orders) throws Exception {
        TableQueryList tableQueryList=new TableQueryList(name,fieldNames,page,condition,orders);
        return getList(tableQueryList);
    }
    private static void fillAttribute(Map<String,Object> map,String a,String attr,Map<String,String> tableAMap,DataRow row) {
        if("oid".equals(attr)){
            map.put(attr, row.get(a + "_oid"));
        }
        int i=attr.indexOf(".");
        if(i==-1){
            Object obj=row.get(a + "_" + attr);
            if(obj instanceof LocalDateTime) obj=Date.from(((LocalDateTime)obj).toInstant(ZoneOffset.of("+8")));
            map.put(attr, obj);
        }else{
            String fn=attr.substring(0, i);
            String newField=attr.substring(i+1);
            Object lm=map.get(fn);
            if(null==lm){
                lm=new HashMap<String,Object>();
            }
            String aa="";
            for(String key : tableAMap.keySet()){
                if(key.equals(a + "." + fn)){
                    aa=tableAMap.get(key);
                    break;
                }
            }
            if(newField.equals("oid")){
                Object tmp=row.get(a + "_" + fn);
                if(null!=tmp){
                    ((HashMap<String,Object>)lm).put("oid", tmp);
                }
            }else{
                fillAttribute((HashMap<String,Object>)lm, aa, newField, tableAMap, row);
            }
            map.put(fn, lm);
        }
    }
    public static void main(String[] a) throws Exception {
        TableQueryList tl=new TableQueryList("manage.model.AdminLogin",new String[]{"*"},null,null
        );
        tl.addCondition(QueryCondition.eq("realname","管理员"));
        List<Map<String,Object>> list=TableQueryList.getList(tl);
        SystemUtil.println(ObjectUtil.toString(list));
//        FromQueryList from= FromQueryList.from("a","manage.model.AdminLogin");
//
//        from.addField("#{a.oid} f1");
//        QueryParameter qp=from.getQueryParameter();
//        SystemUtil.println(qp.getSql());
    }
}
